import flask
import json
import sqlite3

# 数据库路径
DATABASE = "./demo.db"

app = flask.Flask(__name__)
app.config.from_object(__name__)


def connect_db():
    # 连接数据库
    return sqlite3.connect(app.config['DATABASE'])


@app.before_request
def before_request():
    flask.g.db = connect_db()


@app.after_request
def after_request(response):
    flask.g.db.close()
    return response


@app.post("/user")
def post():
    # 插入数据
    body = json.loads(flask.request.data)
    # 提取接口参数
    name = body['name']
    pwd = body['pwd']
    sex = body['sex']
    label = body['label']
    # 执行sql语句
    cur = flask.g.db.execute("insert into users (name,pwd,sex,label) values (?,?,?,?)", [name, pwd, sex, label])
    flask.g.db.commit()
    return str(cur is not None)


@app.put("/user/<int:uid>")
def put(uid):
    # 修改数据
    body = json.loads(flask.request.data)
    pwd = body['pwd']
    sex = body['sex']
    label = body['label']
    # 执行sql语句
    cur = flask.g.db.execute("update users set label=?,sex=?,pwd=? where id = ?",
                             [label, sex, pwd, uid])
    flask.g.db.commit()
    return str(cur is not None)


@app.delete("/user/<int:uid>")
def delete(uid):
    # 删除用户
    cur = flask.g.db.execute("delete from users where id = ?", [uid])
    flask.g.db.commit()
    return str(cur is not None)


@app.get("/user/<int:uid>")
def get(uid):
    # 查询指定用户
    cur = flask.g.db.execute("select * from users where id = ?", [uid])
    return json.dumps([dict((cur.description[idx][0], value)
                            for idx, value in enumerate(row)) for row in cur.fetchall()])


@app.get("/user")
def get_all():
    # 查询用户
    cur = flask.g.db.execute("select * from users")
    return json.dumps([dict((cur.description[idx][0], value)
                            for idx, value in enumerate(row)) for row in cur.fetchall()])


if __name__ == "__main__":
    app.run(host='0.0.0.0', port=8001)
